1. Field of the Invention
The present invention generally relates to data processing in databases and, more particularly, to constructing queries capable of returning classified information related to data in a database.
2. Description of the Related Art
Databases are computerized information storage and retrieval systems. A relational database management system is a computer database management system (DBMS) that uses relational techniques for storing and retrieving data. The most prevalent type of database is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.
Regardless of the particular architecture, a DBMS can be structured to support a variety of different types of operations for a requesting entity (e.g., an application, the operating system or an end user). Such operations can be configured to retrieve, add, modify and delete information being stored and managed by the DBMS. Standard database access methods support these operations using high-level query languages, such as the Structured Query Language (SQL). The term “query” denominates a set of commands that cause execution of operations for processing data from a stored database. For instance, SQL supports four types of query operations, i.e., SELECT, INSERT, UPDATE and DELETE. A SELECT operation retrieves data from a database, an INSERT operation adds new data to a database, an UPDATE operation modifies data in a database and a DELETE operation removes data from a database.
One function supported by query languages, including SQL, is the determination of classified information. Classified information is information related to a specific set of data being stored and managed by the DBMS which is grouped into a plurality of predefined categories. For instance, assume a user who wants to determine information related to the level of education of employees of a company. The education level (EDLEVEL) is expressed in an EMPLOYEE database table of the company as a number of years of education. Assume further that the user wants the education level to be classified according to different education categories, such as “secondary”, “college” and “post graduate”. Therefore, each number of years of education must be grouped into one of these education categories on the basis of conditions defining the categories. For each classified number of years of education, the corresponding education category can then be returned. More specifically, the user may issue the following SQL query against the database table EMPLOYEE:
SELECT EMPNO, FIRSTNAME, MIDINIT, LASTNAME, CASE  WHEN EDLEVEL < 15 THEN ‘SECONDARY’  WHEN EDLEVEL < 19 THEN ‘COLLEGE’  ELSE ‘POST GRADUATE’ ENDFROM EMPLOYEE
The SQL query includes a SELECT statement used to specify the fields to be returned, i.e., the result fields. In this example, the SELECT statement specifies a unique identification number (EMPNO), a first name (FIRSTNAME), a middle initial (MIDINIT) and a last name (LASTNAME). The SQL query further includes a FROM statement indicating that the query is executed against the EMPLOYEE table. Moreover, the SQL query includes a CASE expression for classifying all employees according to their number of years of education. More specifically, if a given employee has less than 15 years of education (EDLEVEL<15), the employee is classified as having a SECONDARY education level. If the given employee has at least 15 years and less than 19 years of education (EDLEVEL<19), the employee is classified as having a COLLEGE education level. If the given employee has at least 19 years of education, the employee is classified as having a POST GRADUATE education level.
However, SQL as a means for getting at classified information suffers in all the same ways that SQL suffers as a language for expressing queries. More specifically, one shortcoming of SQL as a means for getting at classified information is the reliance of SQL on user involvement. For instance, assume a user who executes an initial query against a database. In response to execution of the initial query, the user receives an initial query result showing detailed information related to one or more result fields specified by the initial query. Upon studying the initial query result, the user decides to obtain classified information related to data of one or more interesting result fields. To this end, the user needs to determine the interesting result fields from the initial query and to modify the query text to specify a corresponding CASE expression in the query. Assume further that subsequent to issuance of the modified query and return of the classified information, the user decides to obtain classified information related to data of one or more other interesting result fields of the initial query, or that the user wants to modify the classification of the second query, or to go back to the initial query result. Accordingly, the user would again be required to determine corresponding interesting result fields or all result fields from the initial query and to modify the query text to specify another desired CASE expression, to modify the existing CASE expression or to re-specify the initial query. In other words, for each subsequent query, modified or new query text needs to be provided by the user. Furthermore, for each modification of the query the user needs to retrieve the interesting result fields from the initial query. This is, however, cumbersome and inefficient for the user.
Therefore, there is a need for an improved and more flexible technique for handling queries in a database environment for obtaining classified information.